#!/bin/sh
cd `dirname $0`
source ../config.sh
exec_dir qu_major_quality_report_quality
#专业层面质控点信息
HIVE_DB=assurance33
TARGET_TABLE=qu_major_quality_report_quality

#report_no String comment '报告编号',
#index_no  String comment '二级指标编号',
#index_name String comment '二级指标名称',
#quality_name String comment '质控点名称',
#standard_rate String comment '标准达标率',
#target_rate String comment '目标达标率',
#major_num String comment '质控点适用专业数',
#quality_reason String COMMENT '原因分析（暂时为空）',
#quality_action String  COMMENT '改进措施（暂时为空）',
#quality_effect String  COMMENT '改进成效（暂时为空）',
#create_time String comment '创建时间'

function import_table() {
clear_mysql_data "delete from ${TARGET_TABLE} where substr(report_no,1,9)='${SEMESTER_YEARS}' and substr(report_no,10,1)='${SEMESTERS}';"
find_mysql_data "
         INSERT INTO  ${TARGET_TABLE}(
          report_no,index_no,index_name,quality_name,standard_rate,target_rate,major_num,create_time
         )
             select
                concat('${SEMESTER_YEARS}','${SEMESTERS}','MAJOR')  as report_no,
                a.index_no,
                a.index_name,
                b.quality_name,
                c.standard_val as standard_val,
                c.target_val as target_val,
                count(distinct d.major_no) as major_num,
                FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
                from
                im_index_info a
                left join
                im_quality_info b
                on a.index_no=b.second_index_no
                left join
                im_major_quality_param_value c
                on b.quality_no=c.quality_no
                left join
                im_major_target_standard_record d
                on b.quality_no=d.quality_no
                where a.index_layer='MAJOR' and a.index_level='SECOND' and a.index_status='NORMAL'
                and b.index_layer='MAJOR' and b.quality_status='NORMAL'
                and substr(b.create_time,1,10) between '${BEGIN_TIME}' and '${END_TIME}'
                group by a.index_no,
                a.index_name,
                b.quality_name,
                c.standard_val,
                c.target_val
            "
    fn_log "导入数据 —— 专业层面质控点信息 ${ITEM_KEY}：${HIVE_DB}.${HIVE_TABLE}"



}

function plan_getYearData() {
    find_mysql_data "
    select semester_year,semester,date_format(DATE_FORMAT(begin_time,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') as begin_time,
    date_format(DATE_FORMAT(end_time,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') as end_time
    from base_school_calendar_info where FROM_UNIXTIME(UNIX_TIMESTAMP()) BETWEEN  begin_time and end_time;"| while read -a row
    do
      SEMESTER_YEARS=${row[0]}
      SEMESTERS=${row[1]}
      BEGIN_TIME=${row[2]}
      END_TIME=${row[3]}
      if [ ! -n "$SEMESTER_YEARS" ]; then
         echo "SEMESTER_YEAR IS NULL!"
      else
         echo "SEMESTER_YEAR IS NOT NULL"
         echo ${SEMESTER_YEARS}"=="${SEMESTERS}"=="${BEGIN_TIME}"=="${END_TIME}
        import_table
    fi
    done
}

plan_getYearData
finish

